import os

import pymysql

from Base import GetYamlFile
from Base.GetYamlFile import getYamlPath
from Base.ReadExcel import Read_Excel
from Base.WriteExcel import Write_excel


def order_import():
    yml_list = GetYamlFile.getYamlFile()
    tcorderimportPath = yml_list["tcorderimportPath"]
    PROJECT_ROOT = os.path.dirname(os.path.dirname(__file__))
    REPORT_XLSX_DIR = os.path.join(PROJECT_ROOT, tcorderimportPath)
    readBook = Read_Excel(REPORT_XLSX_DIR)
    wt = Write_excel(REPORT_XLSX_DIR, "Sheet1")
    num = readBook.get_RowNums("Sheet1")
    sql_config = getYamlPath("SqlConfigBasic")
    basic_conn = pymysql.connect(**sql_config)
    basic_cur = basic_conn.cursor()
    sql_config = getYamlPath("SqlConfigAccount")
    account_conn = pymysql.connect(**sql_config)
    account_cur = account_conn.cursor()
    select_sql = '''
                select count(*) from bs_customer where `name` = %s;
        '''
    customer_sql = '''
                    select count(*) from capital_account ca  where ca.customer_name = %(name)s and ca.money < %(money)s;
            '''
    for i in range(1, num):
        name = readBook.read_Excel("Sheet1", i, 5)
        money = readBook.read_Excel("Sheet1", i, 18)
        value = {
            "name": name,
            "money": money
        }
        test = basic_cur.execute(select_sql, name)
        test = basic_cur.fetchall()
        for item in test:
            if item[0] == 0:
                print(name)
                wt.write(i + 1, 6, "")
            else:
                test = account_cur.execute(customer_sql, value)
                test = account_cur.fetchall()
                for item in test:
                    if item[0] != 0:
                        print("有钱，但是钱不够的客户：", name, "需要钱：", money)
                        wt.write(i + 1, 6, "")


if __name__ == '__main__':
    order_import()
